﻿using CL.Dapper.Repository;
using CL.Enum.Common;
using CL.Game.Entity;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace CL.Game.DAL
{
    public class LotterySettlementDAL : DataRepositoryBase<LotterySettlementEntity>
    {
        public LotterySettlementDAL(DbConnectionEnum conenum, IDbConnection Db = null) : base(conenum, Db)
        {
        }

        /// <summary>
        /// 获取一个月内
        /// </summary>
        /// <param name="TicketSourceID"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<LotterySettlementEntity> GetSummaryList(int TicketSourceID, string startTime, string endTime, string LotteryCode = null)
        {
            string sql = "SELECT amt.LotteryCode, CASE WHEN amt.SaleAmount > rat.EndAmount AND rat.EndAmount != 0 THEN rat.EndAmount ELSE amt.SaleAmount - rat.StartAmount END AS SaleAmount, CASE WHEN rat.StartAmount > 0 THEN  0 ELSE amt.WinAmount END AS WinAmount, amt.StartDay, amt.EndDay, lo.LotteryName, (amt.SaleAmount*rat.SaleRate) AS RateAmount, rat.SaleRate FROM ( SELECT AVG(a.LotteryCode)AS LotteryCode, SUM(a.SaleAmount) AS SaleAmount, SUM(a.WinAmount) AS WinAmount, MIN(a.DayDate) AS StartDay, MAX(a.DayDate) AS EndDay, b.SalePointID FROM( SELECT sc.LotteryCode, SUM(et.TicketMoney) AS SaleAmount, SUM(et.WinMoney) AS WinAmount, CONVERT(VARCHAR(10), et.CreateTime, 23) AS DayDate FROM[dbo].[CT_SchemeETickets] et JOIN dbo.CT_Schemes sc ON sc.SchemeID = et.SchemeID AND et.TicketSourceID = 0 WHERE et.TicketStatus IN(2, 8, 10, 11) AND et.CreateTime BETWEEN CONVERT(DATETIME, @startT) AND CONVERT(DATETIME, @endT) GROUP BY sc.LotteryCode, CONVERT(VARCHAR(10), et.CreateTime, 23) ) a JOIN( SELECT CONVERT(VARCHAR(10), StartTime, 23) AS StartTime, CONVERT(VARCHAR(10), ISNULL(EndTime, GETDATE()), 23) AS EndTime, SalePointID, TicketSourceID, LotteryCode FROM[CT_SalePoint] WHERE TicketSourceID = 0 AND SalePointStatus = 2 AND ( EndTime > CONVERT(DATETIME, @startT) OR EndTime IS NULL OR StartTime > CONVERT(DATETIME, @endT))) b ON b.LotteryCode = a.LotteryCode WHERE CONVERT(DATETIME, a.DayDate) BETWEEN CONVERT(DATETIME, b.StartTime) AND CONVERT(DATETIME, b.EndTime) GROUP BY b.SalePointID ) amt JOIN dbo.CT_Lotteries lo ON lo.LotteryCode = amt.LotteryCode JOIN dbo.CT_SalePointRate rat ON rat.SalePointID = amt.SalePointID WHERE rat.StartAmount < amt.SaleAmount  ORDER BY amt.LotteryCode, amt.endDay";
            //string sql = "SELECT amt.LotteryCode, amt.SaleAmount, amt.WinAmount, amt.StartDay, amt.EndDay, amt.SalePointID, lo.LotteryName FROM ( SELECT AVG(a.LotteryCode)AS LotteryCode, SUM(a.SaleAmount) AS SaleAmount, SUM(a.WinAmount) AS WinAmount, MIN(a.DayDate) AS StartDay, MAX(a.DayDate) AS EndDay, b.SalePointID FROM( SELECT sc.LotteryCode, SUM(et.TicketMoney) AS SaleAmount, SUM(et.WinMoney) AS WinAmount, CONVERT(VARCHAR(10), et.CreateTime, 23) AS DayDate FROM[dbo].[CT_SchemeETickets] et JOIN dbo.CT_Schemes sc ON sc.SchemeID = et.SchemeID AND et.TicketSourceID = 0 WHERE et.TicketStatus IN(2, 8, 10, 11) AND et.CreateTime BETWEEN CONVERT(DATETIME, @startT) AND CONVERT(DATETIME, @endT) GROUP BY sc.LotteryCode, CONVERT(VARCHAR(10), et.CreateTime, 23) ) a JOIN( SELECT CONVERT(VARCHAR(10), StartTime, 23) AS StartTime, CONVERT(VARCHAR(10), ISNULL(EndTime, GETDATE()), 23) AS EndTime, SalePointID, TicketSourceID, LotteryCode FROM[CT_SalePoint] WHERE TicketSourceID = 0 AND SalePointStatus = 2 AND (EndTime > CONVERT(DATETIME, @startT) OR EndTime IS NULL OR StartTime > CONVERT(DATETIME, @endT))) b ON b.LotteryCode = a.LotteryCode WHERE CONVERT(DATETIME, a.DayDate) BETWEEN CONVERT(DATETIME, b.StartTime) AND CONVERT(DATETIME, b.EndTime) GROUP BY b.SalePointID ) amt JOIN dbo.CT_Lotteries lo ON lo.LotteryCode = amt.LotteryCode {0} ORDER BY amt.LotteryCode, amt.endDay ;";
            var Parms = new DynamicParameters();
            Parms.Add("@startT", startTime);
            Parms.Add("@endT", endTime);

            string sqlWhere = "";
            if(!string.IsNullOrEmpty(LotteryCode))
            {
                sqlWhere = " WHERE amt.LotteryCode = @LotteryCode ";
                Parms.Add("LotteryCode", LotteryCode);
            }
            sql = string.Format(sql, sqlWhere);
            return base.db.Query<LotterySettlementEntity>(sql, Parms).ToList();
        }

        //public List<>
    }
}
